﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class CommandDetailDAL : ICommandDetailDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public CommandDetailDAL()
        {
        }
        public IList<CommandDetailInfo> ListCommandDetail(CommandDetailInfo dtlsInfo)
        {
            string strSQL;
            int CmdSequence = 0;
            if (dtlsInfo.Command != null)
                CmdSequence = dtlsInfo.Command.Sequence;

            int EmpSequence = 0;
            if (dtlsInfo.Employee != null)
                EmpSequence = dtlsInfo.Employee.Sequence;

            List<CommandDetailInfo> infoList = new List<CommandDetailInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", dtlsInfo.Sequence);
                dbManager.AddParameters(1, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT CMD_DTLS_SEQ, CMD_SEQ, EMP_SEQ, OLD_POS_SEQ, OLD_POS_REF"
                    + ", OLD_POS_NAME, OLD_EXEC_NAME, OLD_EXEC_SALARY, OLD_POS_LINE, OLD_CLASS_SEQ"
                    + ", OLD_JOB_SITE_SEQ, OLD_JOB_SITE_NAME, OLD_WORKING_JOB_SITE_SEQ"
                    + ", OLD_WORKING_JOB_SITE_NAME, OLD_SALARY"
                    + ", NEW_POS_SEQ, NEW_POS_REF, NEW_POS_NAME, NEW_EXEC_NAME, NEW_EXEC_SALARY"
                    + ", NEW_POS_LINE, NEW_CLASS_SEQ, NEW_JOB_SITE_SEQ, NEW_JOB_SITE_NAME"
                    + ", NEW_WORKING_JOB_SITE_SEQ, NEW_WORKING_JOB_SITE_NAME, NEW_SALARY, START_DATE"
                    + ", END_DATE, EDU_HIS_SEQ, MOVE_SEQ, RPT_REMARKS"
                    + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN, UPDATED_BY, UPDATED_WHEN"
                    + " FROM HRM_PS_COMMAND_DTLS"
                    + " WHERE RECORD_STATUS not like @DeleteRecord"
                    + (dtlsInfo.Sequence > 0 ? " AND CMD_DTLS_SEQ = @Sequence" : "")
                    + (EmpSequence > 0 ? " AND EMP_SEQ = @EmpSequence" : "")
                    + (CmdSequence > 0 ? " AND CMD_SEQ = @CmdSequence" : "")
                    + " ORDER BY START_DATE DESC";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    CommandDAL cmdDAL = new CommandDAL();
                    CommandInfo cmdInfo = new CommandInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        cmdInfo.Sequence = rdr.GetInt32(1);
                        cmdInfo.TranType = SystemInfo._CmdEmployee;
                        IList<CommandInfo> cmdList = cmdDAL.ListCommandInfo(cmdInfo);
                        cmdInfo = cmdList[0];
                    }

                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        empInfo.Sequence = rdr.GetInt32(2);
                        //IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(2)));
                        //empInfo = empList[0];
                    }

                    PositionDAL opDAL = new PositionDAL();
                    PositionInfo opInfo = new PositionInfo();
                    if (!rdr.IsDBNull(3))
                    {
                        IList<PositionInfo> opList = opDAL.ListPositions(new PositionInfo(rdr.GetInt32(3)));
                        opInfo = opList[0];
                    }

                    PositionInfo npInfo = new PositionInfo();
                    if (!rdr.IsDBNull(16))
                    {
                        IList<PositionInfo> npList = opDAL.ListPositions(new PositionInfo(rdr.GetInt32(16)));
                        npInfo = npList[0];
                    }

                    ClassDAL cDAL = new ClassDAL();
                    ClassInfo ocInfo = new ClassInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        //IList<ClassInfo> ocList = cDAL.ListClasses(new ClassInfo(rdr.GetInt32(9)));
                        ocInfo = cDAL.GetClassInfo(rdr.GetInt32(9));
                    }
                    ClassInfo ncInfo = new ClassInfo();
                    if (!rdr.IsDBNull(22))
                    {
                        //IList<ClassInfo> ncList = cDAL.ListClasses(new ClassInfo(rdr.GetInt32(22)));
                        ncInfo = cDAL.GetClassInfo(rdr.GetInt32(22));
                    }

                    JobSiteDAL jsDAL = new JobSiteDAL();
                    JobSiteInfo ojInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(11))
                    {
                        IList<JobSiteInfo> ojList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(11)));
                        ojInfo = ojList[0];
                    }
                    JobSiteInfo njInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(24))
                    {
                        IList<JobSiteInfo> njList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(24)));
                        njInfo = njList[0];
                    }
                    JobSiteInfo owInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(13))
                    {
                        IList<JobSiteInfo> owList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(13)));
                        owInfo = owList[0];
                    }
                    JobSiteInfo nwInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(26))
                    {
                        IList<JobSiteInfo> nwList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(26)));
                        nwInfo = nwList[0];
                    }

                    HisEducationDAL eduDAL = new HisEducationDAL();
                    HisEducationInfo eduInfo = new HisEducationInfo();
                    if (!rdr.IsDBNull(31))
                    {
                        IList<HisEducationInfo> eduList = eduDAL.ListHisEducations(new HisEducationInfo(rdr.GetInt32(31)));
                        if (eduList.Count > 0)
                            eduInfo = eduList[0];
                    }

                    MovmentDAL movDAL = new MovmentDAL();
                    MovmentInfo movInfo = new MovmentInfo();
                    if (!rdr.IsDBNull(32))
                    {
                        IList<MovmentInfo> movList = movDAL.ListMovments(new MovmentInfo(rdr.GetInt32(32)));
                        movInfo = movList[0];
                    }

                    CommandDetailInfo Info = new CommandDetailInfo(rdr.GetInt32(0)
                        , cmdInfo, empInfo
                        , opInfo
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                        , rdr.IsDBNull(7) ? 0 : rdr.GetDouble(7)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , ocInfo
                        , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                        , ojInfo
                        , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                        , owInfo
                        , rdr.IsDBNull(14) ? "" : rdr.GetString(14)
                        , rdr.IsDBNull(15) ? 0 : rdr.GetDouble(15) 
                        , npInfo
                        , rdr.IsDBNull(17) ? "" : rdr.GetString(17)
                        , rdr.IsDBNull(18) ? "" : rdr.GetString(18)
                        , rdr.IsDBNull(19) ? "" : rdr.GetString(19)
                        , rdr.IsDBNull(20) ? 0 : rdr.GetDouble(20)
                        , rdr.IsDBNull(21) ? "" : rdr.GetString(21)
                        , ncInfo
                        , rdr.IsDBNull(23) ? "" : rdr.GetString(23)
                        , njInfo
                        , rdr.IsDBNull(25) ? "" : rdr.GetString(25)
                        , nwInfo
                        , rdr.IsDBNull(27) ? "" : rdr.GetString(27)
                        , rdr.IsDBNull(28) ? 0 : rdr.GetDouble(28)
                        , rdr.IsDBNull(29) ? (DateTime?)null : rdr.GetDateTime(29)
                        , rdr.IsDBNull(30) ? (DateTime?)null : rdr.GetDateTime(30)
                        , eduInfo
                        , movInfo
                        , rdr.IsDBNull(33) ? "" : rdr.GetString(33)
                        , rdr.IsDBNull(34) ? "" : rdr.GetString(34)
                        , rdr.IsDBNull(35) ? "" : rdr.GetString(35)
                        , rdr.IsDBNull(36) ? (int?)null : rdr.GetInt32(36)
                        , rdr.IsDBNull(37) ? (int?)null : rdr.GetInt32(37)
                        );

                    infoList.Add(Info);
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<CommandDetailInfo> ListCommandPosition(CommandDetailInfo dtlsInfo)
        {
            string strSQL;
            int CmdSequence = 0;
            if (dtlsInfo.Command != null)
                CmdSequence = dtlsInfo.Command.Sequence;

            int EmpSequence = 0;
            if (dtlsInfo.Employee != null)
                EmpSequence = dtlsInfo.Employee.Sequence;

            List<CommandDetailInfo> infoList = new List<CommandDetailInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", dtlsInfo.Sequence);
                dbManager.AddParameters(1, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT d.CMD_DTLS_SEQ, d.CMD_SEQ, d.EMP_SEQ, d.OLD_POS_SEQ, d.OLD_POS_REF"
                    + ", d.OLD_POS_NAME, d.OLD_EXEC_NAME, d.OLD_EXEC_SALARY, d.OLD_POS_LINE, d.OLD_CLASS_SEQ"
                    + ", null as OLD_CLASS_NAME, d.OLD_JOB_SITE_SEQ, d.OLD_JOB_SITE_NAME, d.OLD_WORKING_JOB_SITE_SEQ"
                    + ", d.OLD_WORKING_JOB_SITE_NAME, d.OLD_SALARY"
                    + ", d.NEW_POS_SEQ, d.NEW_POS_REF, d.NEW_POS_NAME, d.NEW_EXEC_NAME, d.NEW_EXEC_SALARY"
                    + ", d.NEW_POS_LINE, d.NEW_CLASS_SEQ, null as NEW_CLASS_NAME, d.NEW_JOB_SITE_SEQ, d.NEW_JOB_SITE_NAME"
                    + ", d.NEW_WORKING_JOB_SITE_SEQ, d.NEW_WORKING_JOB_SITE_NAME, d.NEW_SALARY, d.START_DATE"
                    + ", d.END_DATE, d.EDU_HIS_SEQ, d.MOVE_SEQ, d.RPT_REMARKS"
                    + ", d.REMARKS, d.RECORD_STATUS, d.CREATED_BY, d.CREATED_WHEN, d.UPDATED_BY, d.UPDATED_WHEN"
                    + " FROM HRM_PS_COMMAND_DTLS d"
                    + " LEFT JOIN HRM_PS_COMMANDS c ON d.CMD_SEQ = c.CMD_SEQ"
                    + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                        + " AND ct.CMD_GROUP_SEQ not like 5"
                    + (dtlsInfo.Sequence > 0 ? " AND d.CMD_DTLS_SEQ = @Sequence" : "")
                    + (EmpSequence > 0 ? " AND d.EMP_SEQ = @EmpSequence" : "")
                    + (CmdSequence > 0 ? " AND d.CMD_SEQ = @CmdSequence" : "")
                    + " ORDER BY d.START_DATE DESC";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    CommandDAL cmdDAL = new CommandDAL();
                    CommandInfo cmdInfo = new CommandInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        cmdInfo.Sequence = rdr.GetInt32(1);
                        cmdInfo.TranType = SystemInfo._CmdEmployee;
                        IList<CommandInfo> cmdList = cmdDAL.ListCommandInfo(cmdInfo);
                        if (cmdList.Count > 0)
                            cmdInfo = cmdList[0];
                    }

                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        empInfo.Sequence = rdr.GetInt32(2);
                        //IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(2)));
                        //empInfo = empList[0];
                    }

                    PositionDAL opDAL = new PositionDAL();
                    PositionInfo opInfo = new PositionInfo();
                    if (!rdr.IsDBNull(3))
                    {
                        IList<PositionInfo> opList = opDAL.ListPositions(new PositionInfo(rdr.GetInt32(3)));
                        opInfo = opList[0];
                    }

                    PositionInfo npInfo = new PositionInfo();
                    if (!rdr.IsDBNull(16))
                    {
                        IList<PositionInfo> npList = opDAL.ListPositions(new PositionInfo(rdr.GetInt32(16)));
                        npInfo = npList[0];
                    }

                    ClassDAL cDAL = new ClassDAL();
                    ClassInfo ocInfo = new ClassInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        //IList<ClassInfo> ocList = cDAL.ListClasses(new ClassInfo(rdr.GetInt32(9)));
                        ocInfo = cDAL.GetClassInfo(rdr.GetInt32(9));
                    }
                    ClassInfo ncInfo = new ClassInfo();
                    if (!rdr.IsDBNull(22))
                    {
                        //IList<ClassInfo> ncList = cDAL.ListClasses(new ClassInfo(rdr.GetInt32(22)));
                        ncInfo = cDAL.GetClassInfo(rdr.GetInt32(22));
                    }

                    JobSiteDAL jsDAL = new JobSiteDAL();
                    JobSiteInfo ojInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(11))
                    {
                        IList<JobSiteInfo> ojList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(11)));
                        ojInfo = ojList[0];
                    }
                    JobSiteInfo njInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(24))
                    {
                        IList<JobSiteInfo> njList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(24)));
                        njInfo = njList[0];
                    }
                    JobSiteInfo owInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(13))
                    {
                        IList<JobSiteInfo> owList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(13)));
                        owInfo = owList[0];
                    }
                    JobSiteInfo nwInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(26))
                    {
                        IList<JobSiteInfo> nwList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(26)));
                        nwInfo = nwList[0];
                    }

                    HisEducationDAL eduDAL = new HisEducationDAL();
                    HisEducationInfo eduInfo = new HisEducationInfo();
                    if (!rdr.IsDBNull(31))
                    {
                        IList<HisEducationInfo> eduList = eduDAL.ListHisEducations(new HisEducationInfo(rdr.GetInt32(31)));
                        if (eduList.Count > 0)
                            eduInfo = eduList[0];
                    }

                    MovmentDAL movDAL = new MovmentDAL();
                    MovmentInfo movInfo = new MovmentInfo();
                    if (!rdr.IsDBNull(32))
                    {
                        IList<MovmentInfo> movList = movDAL.ListMovments(new MovmentInfo(rdr.GetInt32(32)));
                        if (movList.Count > 0)
                            movInfo = movList[0];
                    }

                    CommandDetailInfo Info = new CommandDetailInfo(rdr.GetInt32(0)
                        , cmdInfo, empInfo
                        , opInfo
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                        , rdr.IsDBNull(7) ? 0 : rdr.GetDouble(7)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , ocInfo
                        , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                        , ojInfo
                        , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                        , owInfo
                        , rdr.IsDBNull(14) ? "" : rdr.GetString(14)
                        , rdr.IsDBNull(15) ? 0 : rdr.GetDouble(15)
                        , npInfo
                        , rdr.IsDBNull(17) ? "" : rdr.GetString(17)
                        , rdr.IsDBNull(18) ? "" : rdr.GetString(18)
                        , rdr.IsDBNull(19) ? "" : rdr.GetString(19)
                        , rdr.IsDBNull(20) ? 0 : rdr.GetDouble(20)
                        , rdr.IsDBNull(21) ? "" : rdr.GetString(21)
                        , ncInfo
                        , rdr.IsDBNull(23) ? "" : rdr.GetString(23)
                        , njInfo
                        , rdr.IsDBNull(25) ? "" : rdr.GetString(25)
                        , nwInfo
                        , rdr.IsDBNull(27) ? "" : rdr.GetString(27)
                        , rdr.IsDBNull(28) ? 0 : rdr.GetDouble(28)
                        , rdr.IsDBNull(29) ? (DateTime?)null : rdr.GetDateTime(29)
                        , rdr.IsDBNull(30) ? (DateTime?)null : rdr.GetDateTime(30)
                        , eduInfo
                        , movInfo
                        , rdr.IsDBNull(33) ? "" : rdr.GetString(33)
                        , rdr.IsDBNull(34) ? "" : rdr.GetString(34)
                        , rdr.IsDBNull(35) ? "" : rdr.GetString(35)
                        , rdr.IsDBNull(36) ? (int?)null : rdr.GetInt32(36)
                        , rdr.IsDBNull(37) ? (int?)null : rdr.GetInt32(37)
                        );

                    infoList.Add(Info);
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<CommandDetailInfo> ListCommandSalary(CommandDetailInfo dtlsInfo)
        {
            string strSQL;
            int CmdSequence = 0;
            if (dtlsInfo.Command != null)
                CmdSequence = dtlsInfo.Command.Sequence;

            int EmpSequence = 0;
            if (dtlsInfo.Employee != null)
                EmpSequence = dtlsInfo.Employee.Sequence;

            List<CommandDetailInfo> infoList = new List<CommandDetailInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", dtlsInfo.Sequence);
                dbManager.AddParameters(1, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT d.CMD_DTLS_SEQ, d.CMD_SEQ, d.EMP_SEQ, d.OLD_SALARY"
                    + ", d.NEW_SALARY, d.START_DATE, d.END_DATE, d.MOVE_SEQ, d.RPT_REMARKS"
                    + ", d.REMARKS, d.RECORD_STATUS, d.CREATED_BY, d.CREATED_WHEN, d.UPDATED_BY, d.UPDATED_WHEN"
                    + " FROM HRM_PS_COMMAND_DTLS d"
                    + " LEFT JOIN HRM_PS_COMMANDS c ON d.CMD_SEQ = c.CMD_SEQ"
                    + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                        + " AND ct.CMD_GROUP_SEQ = 5" // ประเภทการเลื่อนเงินเดือน
                    + (dtlsInfo.Sequence > 0 ? " AND d.CMD_DTLS_SEQ = @Sequence" : "")
                    + (EmpSequence > 0 ? " AND d.EMP_SEQ = @EmpSequence" : "")
                    + (CmdSequence > 0 ? " AND d.CMD_SEQ = @CmdSequence" : "")
                    + " ORDER BY d.START_DATE DESC";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    CommandDAL cmdDAL = new CommandDAL();
                    CommandInfo cmdInfo = new CommandInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        cmdInfo.Sequence = rdr.GetInt32(1);
                        cmdInfo.TranType = SystemInfo._CmdEmployee;
                        IList<CommandInfo> cmdList = cmdDAL.ListCommandInfo(cmdInfo);
                        cmdInfo = cmdList[0];
                    }

                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        empInfo.Sequence = rdr.GetInt32(2);
                        //IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(2)));
                        //empInfo = empList[0];
                    }                    

                    MovmentDAL movDAL = new MovmentDAL();
                    MovmentInfo movInfo = new MovmentInfo();
                    if (!rdr.IsDBNull(7))
                    {
                        IList<MovmentInfo> movList = movDAL.ListMovments(new MovmentInfo(rdr.GetInt32(7)));
                        movInfo = movList[0];
                    }

                    CommandDetailInfo Info = new CommandDetailInfo();
                    Info.Sequence = rdr.GetInt32(0);
                    Info.Command = cmdInfo;
                    Info.OldSalary = rdr.IsDBNull(3) ? 0 : rdr.GetDouble(3);
                    Info.NewSalary = rdr.IsDBNull(4) ? 0 : rdr.GetDouble(4);
                    Info.StartDate = rdr.IsDBNull(5) ? (DateTime?)null : rdr.GetDateTime(5);
                    Info.Movment = movInfo;
                    Info.Employee = empInfo;
                    Info.Remarks = rdr.IsDBNull(9) ? "" : rdr.GetString(9);

                    infoList.Add(Info);
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return infoList;
        }
        public DataSet LoadCommandDetails(CommandDetailInfo Info)
        {
            string strSQL;
            int CmdSequence = 0;
            if (Info.Command != null)
                CmdSequence = Info.Command.Sequence;

            int EmpSequence = 0;
            if (Info.Employee != null)
                EmpSequence = Info.Employee.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT d.*"
                        + ", c.CMD_NAME, c.CMD_REF_NO, c.RPT_HEADER, c.CMD_RPT_DATE"
                        + ", cn.CLASS_NAME AS NEW_CLASS_NAME, co.CLASS_NAME AS OLD_CLASS_NAME"
                        + ", en.CLASS_NAME AS ENT_CLASS_NAME"
                        + ", CONCAT(pn.POS_REF, ' - ', pn.POS_NAME) AS NEW_POS_TEXT"
                        + ", CONCAT(po.POS_REF, ' - ', po.POS_NAME) AS OLD_POS_TEXT"
                        + ", pt.POS_TYPE_NAME AS OLD_POS_TYPE_NAME, npt.POS_TYPE_NAME AS NEW_POS_TYPE_NAME"
                        //+ ", "
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN"
                            + " CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                            + " ELSE CONCAT(tnp.PREFIX_NAME, te.FIRST_NAME, '  ', te.LAST_NAME)"
                            + " END AS FULL_NAME"
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN e.CITIZEN"
                            + " ELSE te.CITIZEN"
                            + " END AS CITIZEN"
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN e.BIRTH_DATE"
                            + " ELSE te.BIRTH_DATE"
                            + " END AS BIRTH_DATE"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN q.EDU_QUAL_NAME ELSE tq.EDU_QUAL_NAME END AS EDU_QUAL_NAME"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN m.EDU_MAJOR_NAME ELSE tm.EDU_MAJOR_NAME END AS EDU_MAJOR_NAME"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN ins.EDU_INS_NAME ELSE ti.EDU_INS_NAME END AS EDU_INS_NAME"
                        + ", mm.MOVE_NAME, ct.CMD_TYPE_NAME_SHORT"
                    + " FROM HRM_PS_COMMAND_DTLS d"
                        + " INNER JOIN HRM_PS_COMMANDS c ON d.CMD_SEQ = c.CMD_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_MOVMENTS mm ON d.MOVE_SEQ = mm.MOVE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON d.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_TMP_EMPLOYEES te ON d.TMP_EMP_SEQ = te.TMP_EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES tnp ON te.NAME_PREFIX_SEQ = tnp.NAME_PREFIX_SEQ"

                        + " LEFT JOIN HRM_MP_POSITIONS pn ON d.NEW_POS_SEQ = pn.POS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS po ON d.OLD_POS_SEQ = po.POS_SEQ"

                        + " LEFT JOIN HRM_MP_JOB_SITES jn ON d.NEW_JOB_SITE_SEQ = jn.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES jo ON d.OLD_JOB_SITE_SEQ = jo.JOB_SITE_SEQ"

                        + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON d.OLD_POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_POSITION_TYPES npt ON d.NEW_POS_TYPE_SEQ = npt.POS_TYPE_SEQ"

                        + " LEFT JOIN HRM_MP_CLASSES cn ON d.NEW_CLASS_SEQ = cn.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES co ON d.OLD_CLASS_SEQ = co.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES en ON d.ENT_CLASS_SEQ = en.CLASS_SEQ"

                        + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_EDUCATIONS h ON d.EDU_HIS_SEQ = h.EDU_SEQ"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS q ON h.EDU_QUAL_SEQ = q.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_MAJORS m ON h.EDU_MAJOR_SEQ = m.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES ins ON h.EDU_INS_SEQ = ins.EDU_INS_SEQ"
                        + " LEFT JOIN HRM_TMP_EMPLOYEE_EDUCATIONS th ON d.TMP_EDU_SEQ = th.TMP_EDU_SEQ"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS tq ON th.EDU_QUAL_SEQ = tq.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_MAJORS tm ON th.EDU_MAJOR_SEQ = tm.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES ti ON th.EDU_INS_SEQ = ti.EDU_INS_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                    + (Info.Sequence > 0 ? " AND d.CMD_DTLS_SEQ = @Sequence" : "")
                    + (EmpSequence > 0 ? " AND d.EMP_SEQ = @EmpSequence" : "")
                    + (CmdSequence > 0 ? " AND d.CMD_SEQ = @CmdSequence" : "")
                    + (Info.Group == SystemInfo._HisPosition ? " AND ct.CMD_GROUP_SEQ not like 5" : "")
                    + (Info.Group == SystemInfo._HisSalary ? " AND ct.CMD_GROUP_SEQ = 5" : "")
                    + " ORDER BY d.START_DATE DESC";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet LoadCommandDetailReports(CommandDetailInfo Info)
        {
            string strSQL;
            int CmdSequence = 0;
            if (Info.Command != null)
                CmdSequence = Info.Command.Sequence;

            int EmpSequence = 0;
            if (Info.Employee != null)
                EmpSequence = Info.Employee.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT d.*"
                        + ", c.CMD_NAME, c.CMD_REF_NO, c.RPT_HEADER, c.CMD_RPT_DATE"
                        + ", cn.CLASS_NAME AS NEW_CLASS_NAME, co.CLASS_NAME AS OLD_CLASS_NAME"
                        + ", en.CLASS_NAME AS ENT_CLASS_NAME"
                        + ", CONCAT(pn.POS_REF, ' - ', pn.POS_NAME) AS NEW_POS_TEXT"
                        + ", CONCAT(po.POS_REF, ' - ', po.POS_NAME) AS OLD_POS_TEXT"
                        + ", pt.POS_TYPE_NAME AS OLD_POS_TYPE_NAME"
                        + ", npt.POS_TYPE_NAME AS NEW_POS_TYPE_NAME"
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN"
                            + " CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                            + " ELSE CONCAT(tnp.PREFIX_NAME, te.FIRST_NAME, '  ', te.LAST_NAME)"
                            + " END AS FULL_NAME"
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN e.CITIZEN"
                            + " ELSE te.CITIZEN"
                            + " END AS CITIZEN"
                        + ", CASE WHEN d.EMP_SEQ IS NOT NULL THEN e.BIRTH_DATE"
                            + " ELSE te.BIRTH_DATE"
                            + " END AS BIRTH_DATE"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN q.EDU_QUAL_NAME ELSE tq.EDU_QUAL_NAME END AS EDU_QUAL_NAME"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN m.EDU_MAJOR_NAME ELSE tm.EDU_MAJOR_NAME END AS EDU_MAJOR_NAME"
                        + ", CASE WHEN d.EDU_HIS_SEQ IS NOT NULL THEN ins.EDU_INS_NAME ELSE ti.EDU_INS_NAME END AS EDU_INS_NAME"
                        + ", mm.MOVE_NAME, ct.CMD_TYPE_NAME_SHORT"
                    + " FROM HRM_PS_COMMAND_DTLS d"
                        + " INNER JOIN HRM_PS_COMMANDS c ON d.CMD_SEQ = c.CMD_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_MOVMENTS mm ON d.MOVE_SEQ = mm.MOVE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON d.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_TMP_EMPLOYEES te ON d.TMP_EMP_SEQ = te.TMP_EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES tnp ON te.NAME_PREFIX_SEQ = tnp.NAME_PREFIX_SEQ"

                        + " LEFT JOIN HRM_MP_POSITIONS pn ON d.NEW_POS_SEQ = pn.POS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS po ON d.OLD_POS_SEQ = po.POS_SEQ"

                        + " LEFT JOIN HRM_MP_JOB_SITES jn ON d.NEW_JOB_SITE_SEQ = jn.JOB_SITE_SEQ"

                        + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON d.OLD_POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_POSITION_TYPES npt ON d.NEW_POS_TYPE_SEQ = npt.POS_TYPE_SEQ"

                        + " LEFT JOIN HRM_MP_CLASSES cn ON d.NEW_CLASS_SEQ = cn.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES co ON d.OLD_CLASS_SEQ = co.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES en ON d.ENT_CLASS_SEQ = en.CLASS_SEQ"

                        + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_EDUCATIONS h ON d.EDU_HIS_SEQ = h.EDU_SEQ"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS q ON h.EDU_QUAL_SEQ = q.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_MAJORS m ON h.EDU_MAJOR_SEQ = m.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES ins ON h.EDU_INS_SEQ = ins.EDU_INS_SEQ"

                        + " LEFT JOIN HRM_TMP_EMPLOYEE_EDUCATIONS th ON d.TMP_EDU_SEQ = th.TMP_EDU_SEQ"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS tq ON th.EDU_QUAL_SEQ = tq.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_MAJORS tm ON th.EDU_MAJOR_SEQ = tm.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES ti ON th.EDU_INS_SEQ = ti.EDU_INS_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                    + (Info.Sequence > 0 ? " AND d.CMD_DTLS_SEQ = @Sequence" : "")
                    + (EmpSequence > 0 ? " AND d.EMP_SEQ = @EmpSequence" : "")
                    + (CmdSequence > 0 ? " AND d.CMD_SEQ = @CmdSequence" : "")
                    + (Info.Group == SystemInfo._HisPosition ? " AND ct.CMD_GROUP_SEQ not like 5" : "")
                    + (Info.Group == SystemInfo._HisSalary ? " AND ct.CMD_GROUP_SEQ = 5" : "")
                    + " ORDER BY d.START_DATE DESC";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public void Insert(CommandDetailInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(36);

                dbManager.AddParameters(0, "@cmd_seq", Info.Command.Sequence);
                dbManager.AddParameters(1, "@emp_seq", Info.Employee != null ? Info.Employee.Sequence :(int?)null);
                dbManager.AddParameters(2, "@start_date", Info.StartDate);
                dbManager.AddParameters(3, "@new_pos_seq", Info.NewPosition != null ? Info.NewPosition.Sequence :(int?)null);
                dbManager.AddParameters(4, "@new_pos_ref", Info.NewPositionNo);
                dbManager.AddParameters(5, "@new_pos_name", Info.NewPositionName);
                dbManager.AddParameters(6, "@new_class_seq", Info.NewClass != null ? Info.NewClass.Sequence : (int?)null);
                dbManager.AddParameters(7, "@new_job_site_seq", Info.NewJobSite != null ? Info.NewJobSite.Sequence : (int?)null);
                dbManager.AddParameters(8, "@new_job_site_name", Info.NewJobSiteName);
                dbManager.AddParameters(9, "@new_salary", Info.NewSalary);
                dbManager.AddParameters(10, "@old_salary", Info.OldSalary);
                dbManager.AddParameters(11, "@move_seq", Info.Movment != null ? Info.Movment.Sequence : (int?)null);

                dbManager.AddParameters(12, "@old_pos_seq", Info.OldPosition != null ? Info.OldPosition.Sequence : (int?)null);
                dbManager.AddParameters(13, "@old_pos_ref", Info.OldPositionNo);
                dbManager.AddParameters(14, "@old_pos_name", Info.OldPositionName);
                dbManager.AddParameters(15, "@old_class_seq", Info.OldClass != null ? Info.OldClass.Sequence : (int?)null);
                dbManager.AddParameters(16, "@old_job_site_seq", Info.OldJobSite != null ? Info.OldJobSite.Sequence : (int?)null);
                dbManager.AddParameters(17, "@old_job_site_name", Info.OldJobSiteName);

                dbManager.AddParameters(18, "@ent_no", Info.EntNo);
                dbManager.AddParameters(19, "@ent_pos_name", Info.EntPositionName);
                dbManager.AddParameters(20, "@ent_class_seq", Info.EntClass != null ? Info.EntClass.Sequence : (int?)null);
                dbManager.AddParameters(21, "@ent_pos_type_seq", Info.EntPositionType != null ? Info.EntPositionType.Sequence : (int?)null);
                dbManager.AddParameters(22, "@ent_job_site_name", Info.EntJobSiteName);
                dbManager.AddParameters(23, "@ent_date", Info.EntDate);

                dbManager.AddParameters(24, "@prm_base", Info.PromotionBase);
                dbManager.AddParameters(25, "@prm_percent", Info.PromotionPercent);
                dbManager.AddParameters(26, "@prm_amount", Info.PromotionAmount);

                dbManager.AddParameters(27, "@tmp_emp_seq", Info.TmpEmployee);
                dbManager.AddParameters(28, "@tmp_edu_seq", Info.TmpEducate);
                dbManager.AddParameters(29, "@edu_his_seq", Info.HisEducations != null ? Info.HisEducations.Sequence : (int?)null);

                dbManager.AddParameters(30, "@curr_pos_class_date", Info.CurrentPosClassDate);
                dbManager.AddParameters(31, "@quit_date", Info.QuitDate);

                dbManager.AddParameters(32, "@remarks", Info.Remarks);
                dbManager.AddParameters(33, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(34, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(35, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_COMMAND_DTLS"
                        + "(CMD_SEQ, EMP_SEQ, START_DATE, NEW_POS_SEQ, NEW_POS_REF, NEW_POS_NAME"
                        + ", NEW_CLASS_SEQ, NEW_JOB_SITE_SEQ, NEW_JOB_SITE_NAME, NEW_SALARY, OLD_SALARY, MOVE_SEQ"
                        + ", OLD_POS_SEQ, OLD_POS_REF, OLD_POS_NAME, OLD_CLASS_SEQ, OLD_JOB_SITE_SEQ, OLD_JOB_SITE_NAME"
                        + ", ENT_NO, ENT_POS_NAME, ENT_POS_TYPE_SEQ, ENT_CLASS_SEQ, ENT_JOB_SITE_NAME, ENT_DATE"
                        + ", PRM_BASE, PRM_PERCENT, PRM_AMOUNT, TMP_EMP_SEQ, TMP_EDU_SEQ, EDU_HIS_SEQ"
                        + ", OLD_POS_TYPE_SEQ, NEW_POS_TYPE_SEQ, CURR_POS_CLASS_DATE, QUIT_DATE"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@cmd_seq, @emp_seq, @start_date, @new_pos_seq, @new_pos_ref, @new_pos_name"
                        + ", @new_class_seq, @new_job_site_seq, @new_job_site_name, @new_salary, @old_salary, @move_seq"
                        + ", @old_pos_seq, @old_pos_ref, @old_pos_name, @old_class_seq, @old_job_site_seq, @old_job_site_name"
                        + ", @ent_no, @ent_pos_name"
                        + ", @ent_pos_type_seq"
                        + ", @ent_class_seq, @ent_job_site_name, @ent_date"
                        + ", @prm_base, @prm_percent, @prm_amount, @tmp_emp_seq, @tmp_edu_seq, @edu_his_seq"
                        + ", (select f.pos_type_seq from hrm_mp_positions p"
                                + " left join hrm_mp_field_positions f on p.field_pos_seq = f.field_pos_seq"
                                + " where p.pos_seq = @old_pos_seq)"
                        + ", (select nf.pos_type_seq from hrm_mp_positions np"
                                + " left join hrm_mp_field_positions nf on np.field_pos_seq = nf.field_pos_seq"
                                + " where np.pos_seq = @new_pos_seq)"
                        + ", @curr_pos_class_date, @quit_date"
                        + ", @remarks, @record_status, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void P_Auto_Insert_Salary_Changes(int CmdSequence, int CreatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@CmdSequence", CmdSequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@CreatedBy", CreatedBy);

                strSQL = "CALL P_Auto_Insert_Cmd_SalaryChange(@CmdSequence, @CreatedBy)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Update(CommandDetailInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(35);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@emp_seq", Info.Employee != null ? Info.Employee.Sequence : (int?)null);
                dbManager.AddParameters(2, "@start_date", Info.StartDate);
                dbManager.AddParameters(3, "@new_pos_seq", Info.NewPosition != null ? Info.NewPosition.Sequence : (int?)null);
                dbManager.AddParameters(4, "@new_pos_ref", Info.NewPositionNo);
                dbManager.AddParameters(5, "@new_pos_name", Info.NewPositionName);
                dbManager.AddParameters(6, "@new_class_seq", Info.NewClass != null ? Info.NewClass.Sequence : (int?)null);
                dbManager.AddParameters(7, "@new_job_site_seq", Info.NewJobSite != null ? Info.NewJobSite.Sequence : (int?)null);
                dbManager.AddParameters(8, "@new_job_site_name", Info.NewJobSiteName);
                dbManager.AddParameters(9, "@new_salary", Info.NewSalary);
                dbManager.AddParameters(10, "@old_salary", Info.OldSalary);
                dbManager.AddParameters(11, "@move_seq", Info.Movment != null ? Info.Movment.Sequence : (int?)null);

                dbManager.AddParameters(12, "@old_pos_seq", Info.OldPosition != null ? Info.OldPosition.Sequence : (int?)null);
                dbManager.AddParameters(13, "@old_pos_ref", Info.OldPositionNo);
                dbManager.AddParameters(14, "@old_pos_name", Info.OldPositionName);
                dbManager.AddParameters(15, "@old_class_seq", Info.OldClass != null ? Info.OldClass.Sequence : (int?)null);
                dbManager.AddParameters(16, "@old_job_site_seq", Info.OldJobSite != null ? Info.OldJobSite.Sequence : (int?)null);
                dbManager.AddParameters(17, "@old_job_site_name", Info.OldJobSiteName);

                dbManager.AddParameters(18, "@ent_no", Info.EntNo);
                dbManager.AddParameters(19, "@ent_pos_name", Info.EntPositionName);
                dbManager.AddParameters(20, "@ent_class_seq", Info.EntClass != null ? Info.EntClass.Sequence : (int?)null);
                dbManager.AddParameters(21, "@ent_pos_type_seq", Info.EntPositionType != null ? Info.EntPositionType.Sequence : (int?)null);
                dbManager.AddParameters(22, "@ent_job_site_name", Info.EntJobSiteName);
                dbManager.AddParameters(23, "@ent_date", Info.EntDate);

                dbManager.AddParameters(24, "@prm_base", Info.PromotionBase);
                dbManager.AddParameters(25, "@prm_percent", Info.PromotionPercent);
                dbManager.AddParameters(26, "@prm_amount", Info.PromotionAmount);

                dbManager.AddParameters(27, "@tmp_emp_seq", Info.TmpEmployee);
                dbManager.AddParameters(28, "@tmp_edu_seq", Info.TmpEducate);
                dbManager.AddParameters(29, "@edu_his_seq", Info.HisEducations != null ? Info.HisEducations.Sequence : (int?)null);
                
                dbManager.AddParameters(30, "@curr_pos_class_date", Info.CurrentPosClassDate);
                dbManager.AddParameters(31, "@quit_date", Info.QuitDate);

                dbManager.AddParameters(32, "@remarks", Info.Remarks);
                dbManager.AddParameters(33, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(34, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_COMMAND_DTLS"
                        + " SET EMP_SEQ = @emp_seq"
                        + ", START_DATE = @start_date"
                        + ", NEW_POS_SEQ = @new_pos_seq"
                        + ", NEW_POS_REF = @new_pos_ref"
                        + ", NEW_POS_NAME = @new_pos_name"
                        + ", NEW_CLASS_SEQ = @new_class_seq"
                        + ", NEW_JOB_SITE_SEQ = @new_job_site_seq"
                        + ", NEW_JOB_SITE_NAME = @new_job_site_name"
                        + ", NEW_SALARY = @new_salary"
                        + ", OLD_POS_SEQ = @old_pos_seq"
                        + ", OLD_POS_REF = @old_pos_ref"
                        + ", OLD_POS_NAME = @old_pos_name"
                        + ", OLD_CLASS_SEQ = @old_class_seq"
                        + ", OLD_JOB_SITE_SEQ = @old_job_site_seq"
                        + ", OLD_JOB_SITE_NAME = @old_job_site_name"
                        + ", OLD_SALARY = @old_salary"
                        + ", ENT_NO = @ent_no"
                        + ", ENT_POS_NAME = @ent_pos_name"
                        + ", ENT_POS_TYPE_SEQ = @ent_pos_type_seq"
                        + ", ENT_CLASS_SEQ = @ent_class_seq"
                        + ", ENT_JOB_SITE_NAME = @ent_job_site_name"
                        + ", ENT_DATE = @ent_date"
                        + ", MOVE_SEQ = @move_seq"
                        + ", PRM_BASE = @prm_base"
                        + ", PRM_PERCENT = @prm_percent"
                        + ", PRM_AMOUNT = @prm_amount"
                        + ", TMP_EMP_SEQ = @tmp_emp_seq"
                        + ", TMP_EDU_SEQ = @tmp_edu_seq"
                        + ", EDU_HIS_SEQ = @edu_his_seq"
                        + ", OLD_POS_TYPE_SEQ = (select f.pos_type_seq from hrm_mp_positions p"
                                + " left join hrm_mp_field_positions f on p.field_pos_seq = f.field_pos_seq"
                                + " where p.pos_seq = @old_pos_seq)"
                        + ", NEW_POS_TYPE_SEQ = (select nf.pos_type_seq from hrm_mp_positions np"
                                + " left join hrm_mp_field_positions nf on np.field_pos_seq = nf.field_pos_seq"
                                + " where np.pos_seq = @new_pos_seq)"
                        + ", CURR_POS_CLASS_DATE = @curr_pos_class_date"
                        + ", QUIT_DATE = @quit_date"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_DTLS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_COMMAND_DTLS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_DTLS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
